package com.unis.controller.excelimport;

import java.io.BufferedReader;
import java.io.Reader;
import java.math.BigDecimal;
import java.sql.Clob;
import java.sql.Struct;
import java.text.SimpleDateFormat;
import java.util.*;


import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.unis.common.excel.ImportExcelToData;
import com.unis.common.secure.authc.UserInfo;
import com.unis.common.util.TemplateUtil;
import com.unis.model.system.Code;
import com.unis.model.zhxx.TbJdzjfxSfZh;
import net.sf.ehcache.constructs.scheduledrefresh.OverseerJob;
import net.sf.json.JSONObject;
import oracle.sql.CLOB;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.iterators.ObjectGraphIterator;
import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.lucene.search.spans.SpanWeight;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.aggregation.ArrayOperators;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RequestParam;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;

import com.unis.dto.ResultDto;
import com.unis.common.exception.http.RequestEntityTooLargeException;
import com.unis.common.excel.ExportBeanExcel;
import com.unis.common.util.ValidateUtil;

import com.unis.controller.BaseController;
import com.unis.model.excelimport.UserTableInfo;
import com.unis.service.excelimport.UserTableInfoService;
import org.springframework.web.multipart.MultipartFile;
import sun.awt.image.IntegerInterleavedRaster;

/**
 * @author fdzptwx
 * @version 1.0
 * @since 2020-05-08
 */
@Controller
@RequestMapping("/excelimport/userTableInfo")
public class UserTableInfoController extends BaseController {
	private static final Logger logger = LoggerFactory.getLogger(UserTableInfoController.class);
    private MultipartFile filePart = null;
    private int totalCount = 0;
    private int currentCount = 0;
    @Autowired
    private UserTableInfoService userTableInfoService;
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @RequestMapping("/page")
    public String forwordPage(Model model)  {
        UserInfo userInfo = super.getUserInfo();
        model.addAttribute("username", userInfo.getUsername());
        return "excelimport/userTableInfoPage";
    }

    @RequestMapping( "/list_old")
    @ResponseBody
    public ResultDto queryUserTableInfoList(@RequestParam Map<Object, Object> paramMap){
    	int pageSize = paramMap.get("pageSize")==null ? 10 : Integer.parseInt(paramMap.get("pageSize").toString());
        int pageNumber = paramMap.get("pageNumber")==null ? 1 : Integer.parseInt(paramMap.get("pageNumber").toString());
        try {
            ResultDto page = userTableInfoService.queryListByPage(paramMap,pageNumber,pageSize);
            return page;
        } catch (Exception e) {
            logger.error(e.getMessage());
            throw new RequestEntityTooLargeException(e.getMessage());
        }
    }

    @RequestMapping( "/list")
    @ResponseBody
    public ResultDto queryUserTableInfoListMapByPage(@RequestParam Map<String, String> paramMap, String table_name){
        int pageSize = paramMap.get("pageSize")==null ? 10 : Integer.parseInt(paramMap.get("pageSize").toString());
        int pageNumber = paramMap.get("pageNumber")==null ? 1 : Integer.parseInt(paramMap.get("pageNumber").toString());
        try {
            if (StringUtils.isNotBlank(paramMap.get("sqlType"))){
                Map<String, Object> sqlMap = new HashMap<>();
                sqlMap.put("dic_name", paramMap.get("sqlType"));
                List<Map<String, Object>> sqlStatementList = userTableInfoService.queryAllSqlStatement(sqlMap);
                if (CollectionUtils.isNotEmpty(sqlStatementList)){
                    for (Map<String, Object> sqlm:sqlStatementList) {
                        Clob clob = (Clob) sqlm.get("SQL_STATEMENT");//java.sql.Clob
                        String sql_statement = clobtoString((Clob) clob);
                        sqlm.put("SQL_STATEMENT", sql_statement);
                    }
                    ResultDto page = new ResultDto();
                    page.setRows(sqlStatementList);
                    page.setTotal(sqlStatementList.size());
                    page.setPage(pageNumber);
                    return page;
                }
            }
            int startrow = (pageNumber - 1)*pageSize + 1;
            int endrow = pageNumber*pageSize;
            Map<String, Object> map = new HashMap<>();
            map.put("startrow", startrow);
            map.put("endrow", endrow);
            map.put("table_name", table_name);
            List<Map<String, Object>> list = null;
            /*判断是否有排序字段*/
            UserTableInfo userTableInfoColmunAndChina = new UserTableInfo();
            userTableInfoColmunAndChina.setTable_name(table_name);
            List<Map<String, String>> colmunAndChinaList = userTableInfoService.queryTableCloAndComents(userTableInfoColmunAndChina);
            if (StringUtils.equals(paramMap.get("function_type"), "1") || StringUtils.equals(paramMap.get("function_type"), "6") ||
                StringUtils.equals("5", paramMap.get("function_type"))){
                if (CollectionUtils.isNotEmpty(colmunAndChinaList)){
                    for (Map<String, String> colInfo:colmunAndChinaList) {
                        if (StringUtils.equals(colInfo.get("COMMENTS"), "本地入库时间")){
                            map.put("ordercol", colInfo.get("COLUMN_NAME"));
                        }
                    }
                    map.put("list_col", colmunAndChinaList);
                }
                map.put("order", "desc");
                map.put("colmun_value",paramMap.get("colmun_value"));
                list = userTableInfoService.queryTableDatasByMap(map);
                if (map.get("ordercol") !=null && CollectionUtils.isNotEmpty(list)){
                    for (Map<String, Object> m:list) {
                        m.put((String) map.get("ordercol"), new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(m.get(map.get("ordercol"))));
                    }
                }
            }
            if (StringUtils.equals(paramMap.get("function_type"), "3")){
                if (CollectionUtils.isNotEmpty(colmunAndChinaList)){
                    map.put("list_col", colmunAndChinaList);
                }
                map.put("ordercol","FIELD_ORDER");
                map.put("order", "asc");
                map.put("colmun_value",paramMap.get("colmun_value"));
                Map<String, Object> dataMap = new HashMap<>();
                if (StringUtils.isNotBlank(paramMap.get("selection_edit_table"))){
                    dataMap.put("TABLE_NAME", paramMap.get("selection_edit_table"));
                }
                map.put("dataMap", dataMap);
                list = userTableInfoService.queryTableDatasByMap(map);
            }
            if (StringUtils.equals(paramMap.get("function_type"), "4")){
                if (CollectionUtils.isNotEmpty(colmunAndChinaList)){
                    map.put("list_col", colmunAndChinaList);
                }
                map.put("ordercol","TASK_BATCH");
                map.put("order", "asc");
                map.put("colmun_value",paramMap.get("colmun_value"));
                Map<String, Object> dataMap = new HashMap<>();
                if (StringUtils.isNotBlank(paramMap.get("selection_task_type"))){
                    dataMap.put("TASK_BATCH", paramMap.get("selection_task_type"));
                }
                map.put("dataMap", dataMap);
                list = userTableInfoService.queryTableDatasByMap(map);
                if (CollectionUtils.isNotEmpty(list)){
                    for (Map<String, Object> timeMap:list){
                        if (timeMap.get("TASK_EXECUTION_TIME") != null){
                            timeMap.put("TASK_EXECUTION_TIME", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(timeMap.get("TASK_EXECUTION_TIME")));
                        }
                        if (timeMap.get("CREATE_TIME") != null){
                            timeMap.put("CREATE_TIME", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(timeMap.get("CREATE_TIME")));
                        }
                    }
                }
            }
            Integer totalCountNum = userTableInfoService.queryTableDatasTotalCount(map);
            ResultDto page = new ResultDto();
            page.setRows(list);
            page.setTotal(totalCountNum);
            page.setPage(pageNumber);
            return page;
        } catch (Exception e) {
            logger.error(e.getMessage());
            throw new RequestEntityTooLargeException(e.getMessage());
        }
    }

    @RequestMapping( "/sql_statement_column_list")
    @ResponseBody
    public List<Map<String, Object>> qurySqlStatementList(@RequestParam Map<Object, Object> paramMap, String sqlType){
        try {
            List<Map<String, Object>> list = new ArrayList<>();
            Map<String, Object> map = new LinkedHashMap<>();
            Map<String, Object> map1 = new LinkedHashMap<>();
            map.put("COLUMN_NAME", "SQL_STATEMENT");
            map.put("COMMENTS", "表|视图|存储等SQL结构");
            map1.put("COLUMN_NAME", "OBJECT_TYPE");
            map1.put("COMMENTS", "种类");
            list.add(map);
            list.add(map1);
            return list;
        } catch (Exception e) {
            logger.error(e.getMessage());
            throw new RequestEntityTooLargeException(e.getMessage());
        }
    }
    @RequestMapping( "/listAll")
    @ResponseBody
    public List<UserTableInfo> queryUserTableInfoListAll(@RequestParam Map<?, ?> paramMap, String function_type){
        UserInfo userInfo = super.getUserInfo();
        try {
            if (StringUtils.isBlank(function_type)){
               return new ArrayList<>();
            }
            UserTableInfo userTableInfo = new UserTableInfo();
            userTableInfo.setTable_type(function_type);
            List<UserTableInfo> list = userTableInfoService.queryListAll(userTableInfo);
            return list;
        } catch (Exception e) {
            logger.error(e.getMessage());
            throw new RequestEntityTooLargeException(e.getMessage());
        }
    }

    @RequestMapping( "/list_dic_column")
    @ResponseBody
    public List<JSONObject> queryDicColumn(String table_name){
        try {
            JSONObject resultJson = null;
            Map<String, Object> map = new HashMap<>();
            map.put("table_name", table_name);
            List<JSONObject> resultList = new ArrayList<>();
            List<Map<String, Object>> list = userTableInfoService.queryTableDatas(map);
            if (CollectionUtils.isNotEmpty(list)){
                Map<String, Object> firstMap = list.get(0);
                if (firstMap != null){
                    for (String key : firstMap.keySet()) {
                        resultJson =  new JSONObject();
                        resultJson.put("COLUMN_NAME", key);
                        resultJson.put("COMMENTS", key);
                        resultList.add(resultJson);
                    }
                }
            }
            return resultList;
        } catch (Exception e) {
            logger.error(e.getMessage());
            throw new RequestEntityTooLargeException(e.getMessage());
        }
    }

    @RequestMapping("/table_column_name_list")
    @ResponseBody
    public List<JSONObject> queryTableColumnName(String tableName){
        try {
            UserTableInfo userTableInfo = new UserTableInfo();
            userTableInfo.setTable_name(tableName);
            List<Map<String, String>> list = userTableInfoService.queryTableCloAndComentOrder(userTableInfo);
            List<JSONObject> listJson = new ArrayList<>();
            if (CollectionUtils.isNotEmpty(list)){
                for (Map<String,String> map:list) {
                    if (map != null){
                        JSONObject jsonObject = JSONObject.fromObject(map);
                        listJson.add(jsonObject);
                    }
                }
            }
            return listJson;
        } catch (Exception e) {
            logger.error(e.getMessage());
            throw new RequestEntityTooLargeException(e.getMessage());
        }
    }
    @RequestMapping("/excel")
    public void exportUserTableInfoList(HttpServletResponse response,@RequestParam Map<?, ?> paramMap, String table_name){
        //导出默认1000行
        int pageSize = EXPORT_SIZE;
        int pageNumber = 1;
        OutputStream os = null;
        try {
            int startrow = (pageNumber - 1)*pageSize + 1;
            int endrow = pageNumber*pageSize;
            Map<String, Object> map = new HashMap<>();
            map.put("startrow", startrow);
            map.put("endrow", endrow);
            map.put("table_name", table_name);
            /*判断是否有排序字段*/
            UserTableInfo userTableInfoColmunAndChina = new UserTableInfo();
            userTableInfoColmunAndChina.setTable_name(table_name);
            List<Map<String, String>> colmunAndChinaList = userTableInfoService.queryTableCloAndComents(userTableInfoColmunAndChina);
            if (CollectionUtils.isNotEmpty(colmunAndChinaList)){
                for (Map<String, String> colInfo:colmunAndChinaList) {
                    if (StringUtils.equals(colInfo.get("COMMENTS"), "本地入库时间")){
                        map.put("ordercol", colInfo.get("COLUMN_NAME"));
                    }
                }
            }
            map.put("order", "desc");
            Integer totalCount = userTableInfoService.queryTableDatasTotalCount(map);
            List<Map<String, Object>> list = userTableInfoService.queryTableDatasByMap(map);
            if (CollectionUtils.isNotEmpty(list)){
                for (Map<String, Object> m:list) {
                    m.put((String) map.get("ordercol"), new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(m.get(map.get("ordercol"))));
                }
            }
            ResultDto page = new ResultDto();
            page.setRows(list);
            page.setTotal(totalCount);
            page.setPage(pageNumber);


            response.setContentType("application/vnd.ms-excel");
            response.addHeader("Content-Disposition", "attachment;filename=exportUserTableInfo"+ System.currentTimeMillis() + ".xls");
            os = response.getOutputStream();

            ExportBeanExcel<UserTableInfo> export = new ExportBeanExcel<UserTableInfo>();
            //根据需求选择导出字段，注意数据库中带有_等字符字段修改
            //导出表头
            String[] headers = {"ID","表名","类型","中文名（注释）"};
            //对应字段 headers 与cols一一对应
            String[] cols = {"id","table_name","table_type","comments"};

            /*export.exportExcel("",headers,cols,list,os);*/

            os.flush();
            os.close();
        } catch (Exception e) {
            logger.error(e.getMessage());
            throw new RequestEntityTooLargeException(e.getMessage());
        }
    }

    @RequestMapping("/upload")
    @ResponseBody
    public JSONObject fileUpload(@RequestParam("file") MultipartFile file, HttpServletRequest request) {
        UserInfo account = super.getUserInfo();
        /*Map<String, String> resultMap = new HashMap<>();*/
        JSONObject jsonObject = new JSONObject();
        List<String> headerList = null;
        filePart = file;
        String fileName = "";
        String msg = null;
        if (filePart != null) {
            try {
                fileName = filePart.getOriginalFilename();
                List<List<String>> tableHeader = ImportExcelToData.readExcelHeader(filePart);
                if (CollectionUtils.isNotEmpty(tableHeader)){
                    headerList = tableHeader.get(0);
                }
            } catch (Exception e) {
                logger.error("attachment/upload file to bytes is error.\n" + e);
                logger.debug("错误原因" + e);
                msg = "导入失败，数据写入失败";
                e.printStackTrace();
            }
        } else {
            headerList = new ArrayList<>();
            msg = "导入失败，附件或附件指向为空";
            logger.error(msg);
        }
        jsonObject.put("data", headerList);
        jsonObject.put("fileName", fileName);
        return jsonObject;
    }

    @RequestMapping("/importExcel")
    @ResponseBody
    public Map<String, String> importExcel(@RequestParam(value = "arrayheader[]", required = false)String[] arrayheader,
                                           @RequestParam(value = "arrayheaderdata[]", required = false)String[] arrayheaderdata,
                                           String tableName, HttpServletRequest request) {
        UserInfo account = super.getUserInfo();
        Map<String, String> resultMap = new HashMap<>();
        if (currentCount != 0){
            currentCount = 0;
        }
        if (totalCount != 0){
            totalCount = 0;
        }
        String code = null;
        String msg = null;
        if (filePart != null) {
            try {
                if (arrayheader != null && arrayheader.length != 0 && arrayheaderdata != null && arrayheaderdata.length != 0){
                    /*判断是否有主键*/
                    String primaryKeyColmun = isPrimaryKey(tableName);
                    /*查询字段及注释*/
                    List<Map<String, String>> colmunAndChinaList = getColAndComents(tableName);
                    Map<String, String> colAndChina = new LinkedHashMap<>();
                    Map<String, Integer> batchMap = null;
                    if (CollectionUtils.isNotEmpty(colmunAndChinaList)){
                        for (Map<String, String> colInfo:colmunAndChinaList) {
                            if (StringUtils.equals(colInfo.get("COMMENTS"), "录入人账号")){
                                colAndChina.put("inputAccount", colInfo.get("COLUMN_NAME"));
                            }
                            if (StringUtils.equals(colInfo.get("COMMENTS"), "录入人名称")){
                                colAndChina.put("inputUsername", colInfo.get("COLUMN_NAME"));
                            }
                            if (StringUtils.equals(colInfo.get("COMMENTS"), "本地入库时间")){
                                colAndChina.put("inputTime", colInfo.get("COLUMN_NAME"));
                            }
                            if (StringUtils.equals(colInfo.get("COMMENTS"), "数据来源")){
                                colAndChina.put("dataResource", colInfo.get("COLUMN_NAME"));
                            }
                            if (StringUtils.equals(colInfo.get("COMMENTS"), "有效性")){
                                colAndChina.put("state", colInfo.get("COLUMN_NAME"));
                            }
                            if (StringUtils.equals(colInfo.get("COMMENTS"), "导入批次")){
                                colAndChina.put("importBatch", colInfo.get("COLUMN_NAME"));
                                Map<String, String> pcMap = new HashMap<>();
                                pcMap.put("table_name", tableName);
                                pcMap.put("importbatch", colInfo.get("COLUMN_NAME"));
                                batchMap = userTableInfoService.queryTableDatasMax(pcMap);
                                if (batchMap == null){
                                    batchMap = new HashMap<>();
                                    batchMap.put("IMPORTBATCHNUM", 0);
                                }
                            }
                        }
                    }
                    List<String> headerList = Arrays.asList(arrayheader);
                    List<String> arrayheaderdataList = Arrays.asList(arrayheaderdata);
                    if (CollectionUtils.isNotEmpty(headerList)){
                        List<Map<String, String>> tableDataList = ImportExcelToData.readExcelAllData(filePart, headerList);
                        if (CollectionUtils.isNotEmpty(tableDataList)){
                            totalCount = tableDataList.size();
                            if (totalCount > 100000){
                                resultMap.put("msg","导入数据不能超过"+String.valueOf(100000)+"条");
                                return resultMap;
                            }
                            /*List<String> keyList = new ArrayList<>();*/
                            for (Map<String, String> mapKeyValue : tableDataList) {
                                Map<String, Object> dataMap = new LinkedHashMap<>();
                                Map<String, Object> newMap = new LinkedHashMap<>();
                                for (String keydata : arrayheaderdataList){
                                    if (mapKeyValue != null){
                                        for (String key : mapKeyValue.keySet()) {
                                            if (StringUtils.equals(keydata, key)){
                                                /*keyList.add(key);*/
                                                newMap.put(key, mapKeyValue.get(keydata));
                                            }
                                        }
                                    }
                                }
                                dataMap.put("tablename",tableName);
                                /*dataMap.put("keycol",keyList);*/
                                if (StringUtils.isNotBlank(primaryKeyColmun)){
                                    newMap.put(primaryKeyColmun, TemplateUtil.genUUID());
                                }
                                if (StringUtils.isNotBlank(colAndChina.get("inputAccount"))){
                                    newMap.put(colAndChina.get("inputAccount"), account.getUsername());
                                }
                                if (StringUtils.isNotBlank(colAndChina.get("inputUsername"))){
                                    newMap.put(colAndChina.get("inputUsername"), account.getXm());
                                }
                                if (StringUtils.isNotBlank(colAndChina.get("inputTime"))){
                                    newMap.put(colAndChina.get("inputTime"), new Date());
                                }
                                if (StringUtils.isNotBlank(colAndChina.get("dataResource"))){
                                    newMap.put(colAndChina.get("dataResource"), "0");
                                }
                                if (StringUtils.isNotBlank(colAndChina.get("state"))){
                                    newMap.put(colAndChina.get("state"), "1");
                                }
                                if (StringUtils.isNotBlank(colAndChina.get("importBatch")) && batchMap != null){
                                    newMap.put(colAndChina.get("importBatch"), Integer.valueOf(String.valueOf(batchMap.get("IMPORTBATCHNUM"))) + 1);
                                }
                                dataMap.put("keycolvalue", newMap);
                                userTableInfoService.insertExcelToData(dataMap);
                                currentCount += 1;
                            }
                        }
                    }
                }
                code = "1";
            } catch (Exception e) {
                msg = e.getCause().toString();
                resultMap.put("msg", msg);
                logger.error("attachment/upload file to bytes is error.\n" + e);
                logger.debug("错误原因" + e);
                e.printStackTrace();
            }
        } else {
            logger.error(msg);
        }
        resultMap.put("code", code);
        resultMap.put("msg", msg);
        resultMap.put("currentCount", String.valueOf(currentCount));
        return resultMap;
    }

    @RequestMapping("/importCount")
    @ResponseBody
    public Map<String, Integer> impoerCount(Map map){
        map.put("totalCount", totalCount);
        map.put("currentCount", currentCount);
        return map;
    }

    /*判断是否有主键*/
    public String isPrimaryKey(String tableName) throws Exception{
        UserTableInfo querypraimaryKeyparam = new UserTableInfo();
        querypraimaryKeyparam.setTable_name(tableName);
        List<UserTableInfo> primaryKeyList = userTableInfoService.queryPrimaryKey(querypraimaryKeyparam);
        String primaryKeyColmun = null;
        if (CollectionUtils.isNotEmpty(primaryKeyList) && primaryKeyList.size() <= 1){
            primaryKeyColmun = primaryKeyList.get(0).getColumn_name();
        }
        return primaryKeyColmun;
    }

    /*查询字段及注释*/
    public List<Map<String, String>> getColAndComents(String tableName) throws Exception{
        /*查询字段及注释*/
        UserTableInfo userTableInfoColmunAndChina = new UserTableInfo();
        userTableInfoColmunAndChina.setTable_name(tableName);
        List<Map<String, String>> colmunAndChinaList = userTableInfoService.queryTableCloAndComents(userTableInfoColmunAndChina);
        return colmunAndChinaList;
    }

    public String clobtoString(Clob clob){
        String reString = "";
        Reader is = null;
        try {
            is = clob.getCharacterStream();
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 得到流
        BufferedReader br = new BufferedReader(is);
        String s = null;
        try {
            s = br.readLine();
        } catch (Exception e) {
            e.printStackTrace();
        }
        StringBuffer sb = new StringBuffer();
        while (s != null) {
            // 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
            sb.append(s);
            try {
                s = br.readLine();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        reString = sb.toString();
        return reString;
    }


    @RequestMapping("/save_edit_data")
    @ResponseBody
    public Map<String, String> saveData(String saverows,String table_name, HttpServletRequest request) throws Exception {
        UserInfo account = super.getUserInfo();
        Map<String, String> resultMap = new HashMap<>();
        String code = "";
        String msg = "";
        try {
            if (StringUtils.isNotBlank(table_name)){
                String pk = isPrimaryKey(table_name);
                if (StringUtils.isNotBlank(saverows)){
                    if (StringUtils.isNotBlank(pk)){
                        List<Map> list = com.alibaba.fastjson.JSONObject.parseArray(saverows,Map.class);
                        if (CollectionUtils.isNotEmpty(list)){
                            for (Map map:list){
                                if(map != null){
                                    Map<String, Object> keycolvalue = new LinkedHashMap<>();
                                    keycolvalue.put("tablename", table_name);
                                    for(Object key:map.keySet()){
                                        if (StringUtils.equals(String.valueOf(key),pk)){
                                            if (StringUtils.isBlank(String.valueOf(map.get(key)))){
                                                map.put(key, TemplateUtil.genUUID());
                                                for (Object ki:map.keySet()) {
                                                    if (StringUtils.equals(String.valueOf(ki), "CREATE_TIME")){
                                                        map.put(ki, new Date());
                                                    }
                                                    if (StringUtils.equals(String.valueOf(ki), "TASK_EXECUTION_TIME")){
                                                        map.put(ki, new Date());
                                                    }
                                                    if (StringUtils.equals(String.valueOf(ki), "STATE")){
                                                        map.put(ki, "1");
                                                    }
                                                    if (StringUtils.equals(String.valueOf(ki), "TASK_INTERVAL")){
                                                        map.put(ki, "1");
                                                    }
                                                }
                                                keycolvalue.put("keycolvalue", map);
                                                userTableInfoService.insertExcelToData(keycolvalue);
                                            }
                                            else {
                                                String sqlStr= "";
                                                for (Object k:map.keySet()) {
                                                    if ("CREATE_TIME".equals(String.valueOf(k)) || "TASK_EXECUTION_TIME".equals(String.valueOf(k)) || "ID".equals(String.valueOf(k))){
                                                        continue;
                                                    }else {
                                                        sqlStr += k+"='"+map.get(k)+"'"+",";
                                                    }
                                                }
                                                String sql = " update "+table_name+" set "+sqlStr.substring(0,sqlStr.length()-1)+" where "+key+"='"+map.get(key)+"'";
                                                jdbcTemplate.update(sql);

                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            code = "1";
        } catch (Exception e) {
            code = "0";
            msg = e.getCause().getMessage().toString();
            e.printStackTrace();
        }
        resultMap.put("code", code);
        resultMap.put("msg", msg);
        resultMap.put("currentCount", String.valueOf(currentCount));
        return resultMap;
    }

    @RequestMapping("/delete_tb_data")
    @ResponseBody
    public Map<String, String> deleteData(String saverows,String table_name, HttpServletRequest request) throws Exception {
        UserInfo account = super.getUserInfo();
        Map<String, String> resultMap = new HashMap<>();
        String code = "";
        String msg = "";
        try {
            if (StringUtils.isNotBlank(table_name)){
                String pk = isPrimaryKey(table_name);
                if (StringUtils.isNotBlank(saverows)){
                    if (StringUtils.isNotBlank(pk)){
                        List<Map> list = com.alibaba.fastjson.JSONObject.parseArray(saverows,Map.class);
                        if (CollectionUtils.isNotEmpty(list)){
                            for (Map map:list){
                                if(map != null){
                                    Map<String, Object> keycolvalue = new LinkedHashMap<>();
                                    keycolvalue.put("tablename", table_name);
                                    for(Object key:map.keySet()){
                                        if (StringUtils.equals(String.valueOf(key),pk)){
                                            if (StringUtils.isNotBlank(String.valueOf(map.get(key)))){
                                               String sql = " delete from "+table_name+" where "+key+"='"+map.get(key)+"'";
                                                jdbcTemplate.update(sql);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            code = "1";
        } catch (Exception e) {
            code = "0";
            msg = e.getCause().getMessage().toString();
            e.printStackTrace();
        }
        resultMap.put("code", code);
        resultMap.put("msg", msg);
        resultMap.put("currentCount", String.valueOf(currentCount));
        return resultMap;
    }

    @RequestMapping("/get_field_property_dic")
    @ResponseBody
    public List<Map<String, Object>> getFieldPropertyDic(String dic_type) throws Exception {
        List<Map<String, Object>> resultList = new ArrayList<>();
        Map<String, Object> paraMap = new HashMap<>();
        try {
            paraMap.put("table_name", "FIELD_PROPERTY_DIC_TAB");
            Map<String, Object> dataMap = new HashMap<>();
            paraMap.put("dataMap", dataMap);
            dataMap.put("DIC_TYPE", dic_type);
            List<Map<String, Object>> list = userTableInfoService.queryTableDatas(paraMap);
            if (CollectionUtils.isNotEmpty(list)){
                for (Map<String, Object> m:list) {
                    Map<String, Object> resultMap = new HashMap<>();
                    resultMap.put("text", m.get("DIC_VALUE"));
                    resultMap.put("value", m.get("DIC_VALUE"));
                    resultList.add(resultMap);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return resultList;
        }
        return resultList;
    }

    @RequestMapping("/create_table")
    @ResponseBody
    public Map<String, String> createTable(String sql, String isnull, String table_name) {
        UserInfo account = super.getUserInfo();
        Map<String, String> resultMap = new HashMap<>();
        String code = "";
        String msg = "";
        try {
            if (StringUtils.isNotBlank(sql)){
                if (StringUtils.isNotBlank(isnull) && StringUtils.equals(isnull, "1")){
                    String dropSql = " drop table "+table_name+" CASCADE CONSTRAINTS ";
                    jdbcTemplate.execute(dropSql);
                }
                String[] sqlArray = sql.split(";");
                if (sqlArray != null && sqlArray.length > 0){
                    for (String runSql:sqlArray) {
                        jdbcTemplate.execute(runSql);
                    }
                }
            }
            code = "1";
        } catch (Exception e) {
            code = "0";
            msg = e.getCause().getMessage().toString();
            e.printStackTrace();
        }
        resultMap.put("code", code);
        resultMap.put("msg", msg);
        return resultMap;
    }

    @RequestMapping("/query_create_name_isnull")
    @ResponseBody
    public Map<String, String> queryCreateTableIsnull(String table_name) {
        UserInfo account = super.getUserInfo();
        Map<String, String> resultMap = new HashMap<>();
        String code = "";
        String msg = "";
        try {
            if (StringUtils.isNotBlank(table_name)){
                UserTableInfo userTableInfo = new UserTableInfo();
                userTableInfo.setTable_name(table_name);
                List<Map<String, String>> list = userTableInfoService.queryTableCloAndComentOrder(userTableInfo);
                if (CollectionUtils.isNotEmpty(list)){
                    code = "1";
                }
            }else {
                code = "0";
            }
        } catch (Exception e) {
            code = "0";
            msg = e.getCause().getMessage().toString();
            e.printStackTrace();
        }
        resultMap.put("code", code);
        resultMap.put("msg", msg);
        return resultMap;
    }

    @RequestMapping("/query_create_name_info")
    @ResponseBody
    public Map<String, Object> queryCreateNameInfo() {
        UserInfo account = super.getUserInfo();
        Map<String, Object> resultMap = new HashMap<>();
        List<Map<String, Object>> list= null;
        String code = "";
        String msg = "";
        try {
            list = userTableInfoService.queryCreateTableInfo(new HashMap<>());
            code = "1";
        } catch (Exception e) {
            code = "0";
            msg = e.getCause().getMessage().toString();
            e.printStackTrace();
        }
        resultMap.put("list", list);
        resultMap.put("code", code);
        resultMap.put("msg", msg);
        return resultMap;
    }

    @RequestMapping("/run_sql")
    @ResponseBody
    public Map<String, String> runSql(String sql) {
        UserInfo account = super.getUserInfo();
        Map<String, String> resultMap = new HashMap<>();
        String code = "";
        String msg = "";
        List<Map<String, Object>> list = null;
        try {
            if (StringUtils.isNotBlank(sql)){
                sql = sql.toLowerCase();
                if (sql.indexOf(";") != -1){
                    sql = sql.replace(";","");
                }
                if (sql.startsWith("select")){
                    list = jdbcTemplate.queryForList(sql);
                }else {
                    jdbcTemplate.execute(sql);
                }
            }
            code = "1";
            if (CollectionUtils.isNotEmpty(list)){
                msg = list.toString();
            }
        } catch (Exception e) {
            code = "0";
            msg = e.getCause().getMessage().toString();
            e.printStackTrace();
        }
        resultMap.put("code", code);
        resultMap.put("msg", msg);
        return resultMap;
    }

    @RequestMapping("/query_sql_batch_type_name_info")
    @ResponseBody
    public Map<String, Object> querySqlBatchInfo() {
        UserInfo account = super.getUserInfo();
        Map<String, Object> resultMap = new HashMap<>();
        List<Map<String, String>> list= null;
        String code = "";
        String msg = "";
        try {
            list = userTableInfoService.querySqlBatchInfoList(new HashMap<>());
            code = "1";
        } catch (Exception e) {
            code = "0";
            msg = e.getCause().getMessage().toString();
            e.printStackTrace();
        }
        resultMap.put("list", list);
        resultMap.put("code", code);
        resultMap.put("msg", msg);
        return resultMap;
    }

}
